var express = require('express');
var Msdb = require("../../../db/Mysqldb.js");
var router = express.Router();   //可使用 express.Router 类创建模块化、可挂载的路由句柄

var db = new Msdb();
var getTime=()=>{
    return new Date(new Date().getTime()+8 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');//获取系统时间并转为数据库接受的格式
}

router.post('/selectMenuById',async function(req,res){//通过角色id查询可用菜单
    var menus=[]
    var first_menus = await db.query(`SELECT m.id,title,path,parent_menu_id,is_sys,icon,m.is_show
        FROM menu m
        INNER JOIN user_menu_permission ump 
            ON m.id = ump.menu_id
        WHERE 
            ump.role_id = ?
            AND m.parent_menu_id IS NULL;`,[req.auth.role_id]);//查询一级菜单
    for(var i=0;i<first_menus.length;i++){//根据一级菜单查询对应的信息
        //将标题替换为antd组件中的label
        first_menus[i]={
            ...first_menus[i],
            label:first_menus[i].title,
            key:first_menus[i].title+"&"+first_menus[i].path,
            children:[]
        }
        var second_menus = await db.query(`SELECT * 
            FROM menu m 
            INNER JOIN user_menu_permission ump 
                ON m.id = ump.menu_id
            WHERE 
                ump.role_id = ?
                AND m.parent_menu_id = ?;`,[req.auth.role_id,first_menus[i].id]);//查询二级菜单
        for(var j=0;j<second_menus.length;j++){
            second_menus[j]={
                ...second_menus[j],
                label:second_menus[j].title,
                key:second_menus[j].title+"&"+second_menus[j].path
            }
            first_menus[i].children.push(second_menus[j])
        }
        menus.push(first_menus[i])
    }
    res.send(JSON.stringify(menus));
})

router.post('/selectMenus',async function(req,res){//查询所有菜单
    var menus=await db.query(`SELECT * FROM menu LIMIT ? OFFSET ?;`,[req.body.pageSize,req.body.offset])
    for(var i=0;i<menus.length;i++){
        if(menus[i].parent_menu_id==null){//不存在上一级菜单，跳过
            menus[i].parentMenu={}
        } else {
            var tmp=await db.query(`SELECT * FROM menu WHERE id = ?;`,[menus[i].parent_menu_id])
            menus[i].parentMenu=tmp[0]
        }
    }
    const [countResult] = await db.query(`SELECT COUNT(*) AS total FROM menu`);//统计菜单总数
    menus={
        data:menus,
        total:countResult.total
    }
    res.send(JSON.stringify(menus));
})

router.get('/selectFirstMenus',async function(req,res){//查询可添加子菜单的一级菜单
    var menus=await db.query(`SELECT * FROM menu WHERE parent_menu_id IS NULL AND is_sys=0`)
    res.send(JSON.stringify(menus));
})

router.post('/delete',async function(req,res){//删除某个菜单
    // 获取数据库连接
    const connection = await db.getConnection();
    var result={}
    
    try {
        // 查询菜单是否为系统内置
        const [isSysResult] = await connection.execute(`SELECT is_sys FROM menu WHERE id = ?`,[req.body.menu_id]);
        const is_sys = isSysResult;
        
        if(is_sys[0].is_sys){//系统内置菜单不允许删除
            result={
                code:201,
                msg:"系统内置菜单不允许删除"
            }
        } else {
            
            if(req.auth.role_id!=1){
                result={
                    code:201,
                    msg:"该操作需超级管理员权限"
                }
            } else {//进行删除
                // 开启事务
                await db.beginTransaction(connection);
                
                // 查询子菜单
                const [secondMenusResult] = await connection.execute(`SELECT id FROM menu WHERE parent_menu_id=?`,[req.body.menu_id]);
                const second_menus = secondMenusResult;
                
                // 删除子菜单及其权限
                for(var i=0;i<second_menus.length;i++){
                    await connection.execute(`DELETE FROM menu WHERE id=?`,[second_menus[i].id]);
                    await connection.execute(`DELETE FROM user_menu_permission WHERE menu_id=?`,[second_menus[i].id]);
                }
                
                // 删除主菜单及其权限
                await connection.execute(`DELETE FROM menu WHERE id=?`,[req.body.menu_id]);
                await connection.execute(`DELETE FROM user_menu_permission WHERE menu_id=?`,[req.body.menu_id]);
                
                // 记录操作日志
                var sql_words=`DELETE FROM menu WHERE id=${req.body.menu_id}`;
                var time=getTime();
                await connection.execute(`INSERT INTO operation_log (user_id,sql_words,time) VALUES (?,?,?);`,[req.auth.user_id,sql_words,time]);
                
                // 提交事务
                await db.commit(connection);
                
                result={
                    code:200,
                    msg:"删除成功"
                }
            }
        }
        res.send(JSON.stringify(result));
    } catch (error) {
        // 回滚事务
        await db.rollback(connection);
        console.error('删除菜单错误:', error);
        result={
            code:500,
            msg:"服务器错误，删除菜单失败"
        }
        res.send(JSON.stringify(result));
    } finally {
        // 释放连接
        connection.release();
    }
})

router.post('/insert',async function(req,res){//新增某个菜单
    // 获取数据库连接
    const connection = await db.getConnection();
    var result={}
    
    try {
        if(req.auth.user_id!=1){
            result={
                code:201,
                msg:"只有超级管理员才可以新增菜单"
            }
        } else {
            // 检查菜单名称是否已存在
            const [menusResult] = await connection.execute(`SELECT id FROM menu WHERE title=?`,[req.body.menu.title]);
            const menus = menusResult;
            
            if(menus.length!=0){
                result={
                    code:201,
                    msg:"该菜单名称已存在"
                }
            } else {
                // 开启事务
                await db.beginTransaction(connection);
                
                // 新增菜单
                await connection.execute(`INSERT INTO menu (title,path,parent_menu_id) VALUES (?,?,?)`,
                    [req.body.menu.title, req.body.menu.path, req.body.menu.parent_menu_id]
                );
                
                // 获取新增菜单ID
                const [menuIdResult] = await connection.execute(`SELECT id FROM menu WHERE title=?`,[req.body.menu.title]);
                const menu_id = menuIdResult;
                
                // 更新权限菜单
                await connection.execute(`INSERT INTO user_menu_permission (role_id,menu_id) VALUES (?,?)`,
                    [1, menu_id[0].id]
                );
                
                // 记录操作日志
                var sql_words=`INSERT INTO menu (title,path,parent_menu_id) VALUES (${req.body.menu.title},${req.body.menu.path},${req.body.menu.parent_menu_id})`;
                var time=getTime();
                await connection.execute(`INSERT INTO operation_log (user_id,sql_words,time) VALUES (?,?,?);`,
                    [req.auth.user_id, sql_words, time]
                );
                
                // 提交事务
                await db.commit(connection);
                
                result={
                    code:200,
                    msg:"新增成功"
                }
            }
        }
        res.send(JSON.stringify(result));
    } catch (error) {
        // 回滚事务
        await db.rollback(connection);
        console.error('新增菜单错误:', error);
        result={
            code:500,
            msg:"服务器错误，新增菜单失败"
        }
        res.send(JSON.stringify(result));
    } finally {
        // 释放连接
        connection.release();
    }
})

router.post('/getMenuInfoById',async function(req,res){//根据id查询某个菜单，具体为菜单自身信息与上级菜单信息
    var result={}
    var data=req.body
    if(data.id==undefined){
        result={
            code:201,
            msg:"菜单id不存在"
        }
    } else {
        var menu=await db.query(`SELECT * FROM menu WHERE id=?`,[data.id])//查询菜单自身
        var parentMenu=[]
        if(menu[0].parent_menu_id!=null){
            parentMenu=await db.query(`SELECT * FROM menu WHERE id=?`,[menu[0].parent_menu_id])//查询父级菜单
        }
        result={
            code:200,
            data:{
                menu:menu[0],
                parentMenu:parentMenu[0]
            }
        }
    }
    res.send(JSON.stringify(result));
})

router.post('/edit',async function(req,res){//编辑某个菜单
    // 获取数据库连接
    const connection = await db.getConnection();
    var result={}
    var data=req.body
    
    try {
        if(data.menu.id==data.parentMenu.id){
            result={
                code:201,
                msg:"不能自己作为自己的父级菜单"
            }
            res.send(JSON.stringify(result));
            return
        }
        
        if(req.auth.user_id!=1){
            result={
                code:201,
                msg:"只有超级管理员才可以编辑菜单"
            }
        } else {
            // 检查菜单名称是否已存在
            const [menusResult] = await connection.execute(`SELECT id FROM menu WHERE title=? AND id!=?`,
                [data.menu.title, data.menu.id]
            );
            const menus = menusResult;
            
            if(menus.length!=0){
                result={
                    code:201,
                    msg:"该菜单名称已存在"
                }
            } else {
                // 开启事务
                await db.beginTransaction(connection);
                
                // 更新菜单
                await connection.execute(`UPDATE menu SET title=?,path=?,parent_menu_id=? WHERE id=?`,
                    [data.menu.title, data.menu.path, data.parentMenu.id, data.menu.id]
                );
                
                // 记录操作日志
                var sql_words=`UPDATE menu SET title=${data.menu.title},path=${data.menu.path},parent_menu_id=${data.parentMenu.id} WHERE id=${data.menu.id}`;
                var time=getTime();
                await connection.execute(`INSERT INTO operation_log (user_id,sql_words,time) VALUES (?,?,?);`,
                    [1, sql_words, time]
                );
                
                // 提交事务
                await db.commit(connection);
                
                result={
                    code:200,
                    msg:"更新成功"
                }
            }
        }
        res.send(JSON.stringify(result));
    } catch (error) {
        // 回滚事务
        await db.rollback(connection);
        result={
            code:500,
            msg:"服务器错误，编辑菜单失败"
        }
        res.send(JSON.stringify(result));
    } finally {
        // 释放连接
        connection.release();
    }
})

module.exports = router;